set more off
* do file to creaste total exports at the HS4 product level *
* source data are taken from EUROSTAT *


forv i=2012/2019 {	
	use "../rawdata/trade_`i'",clear
	keep if flow=="exports"
	rename partner land
	drop reporter
	tostring cn8, gen(cncode) format(%08.0f)
	
	** aggregating over stat regime**

	rename land cty_code
	collapse (sum) v,by(cncode cty_code)

	** aggregating by HS4 **

	gen hs4=substr(cncode,1,4)	
	collapse (sum) totxvalue=v,by(hs4 cty_code)
	label var totxvalue "export value at hs4 destination"
	gen year=`i'
	save temp/X_hs4_`i',replace

	}


** appending the years **
clear
forv i=2012/2019 {	
		append using temp/X_hs4_`i'
	}
	
save ../data/X_hs4_2012_2019,replace


********************************************************
*IMPORTS
****************************

forv i=2012/2019 {	
	use "../rawdata/trade_`i'",clear
	keep if flow=="imports"
	rename partner land
	drop reporter
	tostring cn8, gen(cncode) format(%08.0f)
	
	** aggregating over stat regime**

	rename land cty_code
	collapse (sum) v, by(cncode cty_code)

	** aggregating by HS4 **

	gen hs4=substr(cncode,1,4)	
	collapse (sum) totmvalue=v,by(hs4 cty_code)
	label var totmvalue "import value at hs4 destination"
	gen year=`i'
	save temp/M_hs4_`i',replace

	}


** appending the years **
clear
forv i=2012/2019 {	
		append using temp/M_hs4_`i'
	}
	
save ../data/M_hs4_2012_2019,replace


*erase temp files
clear
forv i=2012/2019 {	
	erase temp/X_hs4_`i'.dta
	erase temp/M_hs4_`i'.dta
	}
